<?php
namespace app\api\controller\audience;
use app\api\controller\common\ApiBase;
use Think\Db; 
use app\api\controller\common\CommonPlus;
use PHPExcel_IOFactory;
use PHPExcel;
use app\api\model\Admin as AdminModel;
use think\Controller; // dengh quxiao
// Controller  ApiBase （dengl）
class Scene extends ApiBase
{
    public function _initialize()
    {
        parent::_initialize();
        $this->admin = session('admin');
    }

    public function getTabName($exhibition_id){
        $result=db('exhibition')->where(['id'=>$exhibition_id])->find();
        $tabName='';
        if($result){
            $tabName=$result['create_person_table'];
        }else{
            return json(['code'=>0,'msg'=>'缺少关键字段','data'=>'']);
        }
        return $tabName;
    }

    /**
     * 预登记观众列表  0527
     * @return \think\response\Json|void
     *  // zh_20200528fmr
     */

    public function index(){
        $page = $this->request->param('page');
        $page = max(1,intval($page));
        $pagesize =$this->request->param('pageSize')==""?20:$this->request->param('pageSize');
        $exhibition_id =$this->request->param('exhibition_id');
        $status=$this->request->param('status')==''?1:$this->request->param('status');
        $examine_type=$this->request->param('examine_type');
        if(empty($exhibition_id)){
            return json(['code'=>0,'msg'=>'缺少关键字段','data'=>'']);
        }
        $tabName=$this->getTabName($exhibition_id);
        $tabName='crm_'.$tabName;
        //back字段 0代表正常 1代表退回数据
        //status前端传入1代表 全部 2代表 未联系 3代表 已联系  is_beforehand 0 自动装填系统默认 1 潜在观众 2团体观众 3、现场观众
        $param=" and p.is_beforehand=4 ";
        if($examine_type == 2){
            $param.=" and (p.examine_type=0 or p.examine_type=2)";
        }else if($examine_type == 3){
            $param.=" and p.examine_type=1";
        }
        $keyword =input('keyword');
        if (!empty($keyword)) {
            $param.=" and (p.company_name like '%$keyword%' or p.name_cn like '%$keyword%')";
        }
        $sql2="select p.id,p.examine_type,p.is_beforehand,p.create_time,p.person_id,p.id_positive,p.id_side,p.question,p.mobile_person,p.data_from,p.company_name,p.area_city,p.info_level,p.name_cn,p.company_address,p.link,p.contact,p.category_id,p.second_category_id,p.back,p.parent_id,p.channel_id";
        $sql2.=" from ".$tabName." as p ";
        $sql2.=" where 1=1 $param";
        $sql2.=" limit ".($page-1)*$pagesize.",$pagesize";
        $list=Db::query($sql2);
        $sql21=" select count(p.id) as count1 ";
        $sql21.=" from ".$tabName." as p where 1=1 $param ";
        $total1=Db::query($sql21);
        $total=$total1[0]['count1']; //st
        return json(['code'=>1,'msg'=>'success','data'=>['list'=>$list,'count'=>$total]]);
    }

    //审核
    public function examine(){
        $exhibition_id =$this->request->param('exhibition_id');
        $ids=$this->request->param('ids');
        if(empty($exhibition_id)){
            return json(['code'=>0,'msg'=>'展会数据有误','data'=>'']);
        }
        if(empty($ids)){
            return json(['code'=>0,'msg'=>'观众数据有误','data'=>'']);
        }
        $tabName=$this->getTabName($exhibition_id);
        $person_id = explode(',',$ids);
        foreach ($person_id as &$v){
            $res=db($tabName)->where(['id'=>$v])->find();
            if($res && $res['examine_type']!=1){
                $res=db($tabName)->where(['id'=>$v])->update(['examine_type'=>1]);
            }
        }
        unset($v);
        return json(['code'=>1,'msg'=>'操作成功','data'=>'']);
    }


    // 导出  stj ce
    public function export()
    {
        $page = $this->request->param('page');
        $page = max(1,intval($page));
        $pageSize =$this->request->param('pageSize')==""?20:$this->request->param('pageSize');
        $exhibition_id =$this->request->param('exhibition_id');
        $examine_type=$this->request->param('examine_type');
        $ids=$this->request->param('ids');
        $keyword =input('keyword');
        if(empty($exhibition_id)){
            return json(['code'=>0,'msg'=>'缺少关键字段','data'=>'']);
        }
        $tabNames=$this->getTabName($exhibition_id);
        $tabName='crm_'.$tabNames;
        $param=" and is_beforehand=2 ";
        if(empty($ids)){
            if($examine_type == 2){
                $param.=" and (examine_type=0 or examine_type=2)";
            }else if($examine_type == 3){
                $param.=" and examine_type=1";
            }

            if (!empty($keyword)) {
                $param.=" and (company_name like '%$keyword%' or name_cn like '%$keyword%')";
            }
        }else {
            $param.=" and id in ($ids)";
        }

        $sql2="select id,contact_time,contact_person_name,contact_person,status,name_cn,name_en,mobile_person,email,country,province,area_city,area,company_name,company_name_en,industrys,department_cn,positions_cn";
        $sql2.=" from ".$tabName." where 1=1 $param";
        $sql2.=" limit ".($page-1)*$pageSize.",$pageSize";
        $list=Db::query($sql2);
        $columns = array(
            array('title' => 'ID', 'field' => 'id', 'width' => 12),
            array('title' => '姓名', 'field' => 'name_cn', 'width' => 12),
            array('title' => '英文姓名', 'field' => 'name_en', 'width' => 12),
            array('title' => '电话', 'field' => 'mobile_person', 'width' => 12),
            array('title' => '电子邮件', 'field' => 'email', 'width' => 12),
            array('title' => '国家', 'field' => 'country', 'width' => 12),
            array('title' => '省份', 'field' => 'province', 'width' => 24),
            array('title' => '城市', 'field' => 'area_city', 'width' => 24),
            array('title' => '详细地址', 'field' => 'area', 'width' => 24),
            array('title' => '公司名称', 'field' => 'company_name', 'width' => 12),
            array('title' => '公司名称英文', 'field' => 'company_name_en', 'width' => 12),
            array('title' => '行业', 'field' => 'industrys', 'width' => 24),
            array('title' => '部门', 'field' => 'department_cn', 'width' => 24),
            array('title' => '职位', 'field' => 'positions_cn', 'width' => 24),

        );
        $title = "团体观众明细";
        $filename = $title . date('Y-m-d-H-i', time()) . ".xls";
        return json(['code'=>1,'msg'=>'操作成功','data'=>['columns'=>$columns,'filename'=>$filename,'list'=>$list]]);
    }


    public function saveExcel(){
        $filename=$this->request->param('filename');
        $exhibition_id =$this->request->param('exhibition_id');
        if(empty($filename)){
            return json(['code'=>0,'msg'=>'excel缺失，请重新上传','data'=>'']);
        }
        if(empty($exhibition_id)){
            return json(['code'=>0,'msg'=>'缺少关键字段','data'=>'']);
        }
        $tabName=$this->getTabName($exhibition_id);
        $filename = ROOT_PATH.'public/uploads/excel/'.$filename;
        $reader = PHPExcel_IOFactory::createReaderForFile($filename);
        $excel = $reader->load($filename, $encode = 'utf-8');
        $excel_array=$excel->getsheet(0)->toArray();  //转换为数组格式
        array_shift($excel_array);  //删除第一个数组(标题);
        if (!is_array($excel_array) || empty($excel_array)) {
            return false;
        } else {
            //循环读取数据
            foreach ($excel_array as $key => $value) {
                $list[$key]['name_cn'] = (string) $value[1];//中文姓名
                $list[$key]['name_en'] = (string) $value[2]; //英文姓名
                $list[$key]['mobile_person'] = (string) $value[3]; //电话
                $list[$key]['email'] = (string) $value[4]; // 邮件
                $list[$key]['country'] = (string) $value[5]; // 国家
                $list[$key]['province'] = (string) $value[6]; // 省份
                $list[$key]['area_city'] = (string) $value[7]; // 城市
                $list[$key]['area'] = (string) $value[8]; // 详细地址
                $list[$key]['company_name'] = (string)$value[9]; // 公司名称
                $list[$key]['company_name_en'] = (string)$value[10]; // 英文名称
                $list[$key]['industrys'] = (string)$value[11]; // 行业
                $list[$key]['department_cn'] = (string)$value[12]; // 部门
                $list[$key]['positions_cn'] = (string)$value[13]; // 职位
            }
        }
        foreach ($list as &$v){
            $pub=db('company_user')->where(['mobile_person'=>$v['mobile_person']])->find();
            $v['examine_type']=1;
            $v['is_beforehand']=2;
            if($pub){
                $per=db($tabName)->where(['person_id'=>$pub['id']])->find();
                if($per){
                    db($tabName)->where(['id'=>$per['id']])->update($v);
                }else{
                    $v['person_id']=$pub['id'];
                    db($tabName)->insert($v);
                }
            }else{
                $r = db('company_user')->insertGetId($v);
                $v['person_id']=$r;
                db($tabName)->insert($v);
            }
            unset($v);
        }
        
        return json(['code' => 1, 'msg' => '操作成功']);
    }

}